package sqlGo

import (
	"errors"
	"fmt"
	"log"

	"database/sql"
	//导入数据库驱动
	_ "github.com/go-sql-driver/mysql"
)

//QueryRow 根据SQL语句，参数查询单条记录
func QueryRow(sqlStr string, args ...interface{}) (rowRecord RowRecord, err error) {
	rows, err := QueryRows(sqlStr, args)
	if err != nil {
		return nil, err
	}
	if len(rows) > 0 {
		return rows[0], nil
	}
	return nil, nil

}

//QueryRows  根据参数SQL，参数查询多条记录
func QueryRows(sqlStr string, args ...interface{}) (rowRecords RowRecords, err error) {
	if "" == sqlStr {
		return nil, errors.New("传入的SQL语句不能为空！")
	}

	rows, err := conn.Query(sqlStr, args...)

	//调用go-sql-server\Mysql驱动查询

	//db, _ = dbMp["default"]
	if err != nil {
		log.Println("mysql query error", err.Error())
		return nil, err
	}
	//延时关闭Rows
	defer rows.Close()
	//获取记录列
	columns, err := rows.Columns()
	if err != nil {
		log.Println(err.Error())
		return nil, err
	}
	//拼接记录Map
	values := make([]sql.RawBytes, len(columns))
	scans := make([]interface{}, len(columns))
	for i := range values {
		scans[i] = &values[i]
	}
	for rows.Next() {
		_ = rows.Scan(scans...)
		each := make(RowRecord)
		for i, col := range values {
			each[columns[i]] = string(col)
		}
		rowRecords = append(rowRecords, each)
		//仅读取第一条记录
	}
	return rowRecords, nil

}

//FindRecordByID 根据表名主键，查询记录
func FindRecordByID(tableName, priKey string, value interface{}) (row RowRecord, err error) {
	if tableName == "" {
		return nil, errors.New("表名不能为空！")
	}
	if priKey == "" {
		return nil, errors.New("主键不能为空")
	}
	sqlStr := "SELECT * FROM " + tableName + " WHERE " + priKey + " = ? "
	row, err = QueryRow(sqlStr, value)
	if err != nil {
		return nil, err

	}
	return row, nil
}

//FindRecords 根据表名，查询多条记录
func FindRecords(tableName string) (rows RowRecords, err error) {
	if tableName == "" {
		return nil, errors.New("表名不能为空！")
	}
	sqlStr := "SELECT * FROM " + tableName
	rows, err = QueryRows(sqlStr)
	if err != nil {
		return nil, err
	}
	return rows, nil
}

//SaveRecord 保存Record记录
func SaveRecord(tableName string, priKey string, row RowRecord) (rowCount int64, err error) {
	if tableName == "" || priKey == "" {
		return -1, errors.New("表或主键不能为空")
	}
	var mo SingleRowModel
	key := row.GetString(priKey, "")
	if key != "" {
		//更新
		mo, err = createUpMapModel(tableName, priKey, row)
	} else {
		//新增
		row[priKey] = UUID()
		mo, err = createNewMapModel(tableName, priKey, row)
	}
	if err != nil {
		return -1, err
	}
	return ExecuteSingleModel(mo)

}

//SaveMap 保存SaveMap记录
func SaveMap(tableName string, priKey string, rowMap map[string]interface{}) (rowCount int64, err error) {
	if tableName == "" || priKey == "" {
		return -1, errors.New("表或主键不能为空")
	}
	var mo SingleRowModel
	value, ok := rowMap[priKey]
	if ok {
		//更新
		//判断value 是不是为空
		valueTemp := String(value)
		if valueTemp == "" {
			//新增
			rowMap[priKey] = UUID()
			mo, err = createNewMapModel(tableName, priKey, rowMap)
		} else {
			//更新
			mo, err = createUpMapModel(tableName, priKey, rowMap)
		}
	} else {
		//新增
		rowMap[priKey] = UUID()
		mo, err = createNewMapModel(tableName, priKey, rowMap)
	}
	if err != nil {
		return -1, err
	}
	return ExecuteSingleModel(mo)
}

//SaveRecords 保存批量记录
func SaveRecords(tableName string, priKey string, rows []RowRecord) (rowCount int64, err error) {
	if tableName == "" || priKey == "" {
		return -1, errors.New("表或主键不能为空")
	}
	if 0 == len(rows) {
		return -1, errors.New("模型记录不能为空！")
	}
	mos := []SingleRowModel{}
	var mo SingleRowModel
	var resErr error
	flag := true
	for _, row := range rows {
		key := row.GetString(priKey, "")
		if key != "" {
			//更新
			mo, err = createUpRecordModel(tableName, priKey, row)
			resErr = err
			if err != nil {
				flag = false
				break
			}
		} else {
			//新增
			row[priKey] = UUID()
			mo, err = createNewRecordModel(tableName, priKey, row)
			if err != nil {
				flag = false
				break
			}
		}
		mos = append(mos, mo)
	}
	//中间出错
	if !flag {
		return -1, resErr
	}
	//执行mos
	return ExecuteSingleModels(mos)

}

//SaveMaps 批量保存记录
func SaveMaps(tableName string, priKey string, rows []map[string]interface{}) (rowCount int64, err error) {
	if tableName == "" || priKey == "" {
		return -1, errors.New("表或主键不能为空")
	}
	if 0 == len(rows) {
		return -1, errors.New("模型记录不能为空！")
	}
	mos := []SingleRowModel{}
	var mo SingleRowModel
	var resErr error
	flag := true
	for _, row := range rows {
		value, ok := row[priKey]
		if ok {
			//更新
			//判断value 是不是为空
			valueTemp := String(value)
			if valueTemp == "" {
				//新增
				row[priKey] = UUID()
				mo, err = createNewMapModel(tableName, priKey, row)
				resErr = err
				if err != nil {
					flag = false
					break
				}
			} else {
				//更新
				mo, err = createUpMapModel(tableName, priKey, row)
				resErr = err
				if err != nil {
					flag = false
					break
				}
			}
		} else {
			//新增
			row[priKey] = UUID()
			mo, err = createNewMapModel(tableName, priKey, row)
			resErr = err
			if err != nil {
				flag = false
				break
			}
		}
		mos = append(mos, mo)
	}
	//中间出错
	if !flag {
		return -1, resErr
	}
	//执行mos
	return ExecuteSingleModels(mos)
}

//DelRow 根据表名，主键、值删除记录
func DelRow(tableName string, priKey string, value interface{}) (rowCount int64, err error) {
	sql := "DELETE FROM " + tableName + " WHERE " + priKey + " = ? "
	tx, err := conn.Begin()
	if err != nil {
		return -1, err
	}
	smt, err := tx.Prepare(sql)
	if err != nil {
		return -1, err
	}
	rs, err := smt.Exec(value)
	if err != nil {
		err = tx.Rollback()
		return -1, err
	}
	rowCount, err = rs.RowsAffected()
	if err != nil {
		errBack := tx.Rollback()
		if errBack != nil {
			return -1, errBack
		}
		return -1, err
	}
	commitErr := tx.Commit()
	if commitErr != nil {
		return -1, commitErr
	}
	return rowCount, nil
}

//ExecuteSingleModel 保存单模型单条记录
func ExecuteSingleModel(model SingleRowModel) (rowCount int64, err error) {
	//if nil == model {
	//	return -1, errors.New("模型不能为空！")
	//}
	if model.SQL == "" {
		return -1, errors.New("模型的SQL语句不能为空！")
	}
	tx, err := conn.Begin()
	if err != nil {
		return -1, err
	}
	smt, err := tx.Prepare(string(model.SQL))
	if err != nil {
		return -1, err
	}
	rs, err := smt.Exec(model.Args...)
	if err != nil {
		err = tx.Rollback()
		return -1, err
	}
	rowCount, err = rs.RowsAffected()
	if err != nil {
		errBack := tx.Rollback()
		if errBack != nil {
			return -1, errBack
		}
		return -1, err
	}
	commitErr := tx.Commit()
	if commitErr != nil {
		return -1, commitErr
	}
	return rowCount, nil
}

//ExecuteMultiModel 保存单模型多条记录
func ExecuteMultiModel(multiModel MultiRowModel) (rowCount int64, err error) {
	var resErr error
	//if multiModel == nil  {
	//	return -1, errors.New("模型不能为空！")
	//}
	if multiModel.SQL == "" {
		return -1, errors.New("模型的SQL语句不能为空！")
	}
	tx, err := conn.Begin()
	if err != nil {
		return -1, err
	}
	smt, err := tx.Prepare(string(multiModel.SQL))
	if err != nil {
		return -1, err
	}
	rowCount = 0
	commitFlag := true
	for _, args := range multiModel.Args {
		rs, err := smt.Exec(args...)
		resErr = err
		if err != nil {
			commitFlag = false
			break
		}
		count, err := rs.RowsAffected()
		resErr = err
		if err != nil {
			commitFlag = false
			break
		}
		rowCount += count
	}
	if commitFlag {
		commitErr := tx.Commit()
		if commitErr != nil {
			return -1, commitErr
		}
		return rowCount, nil
	}
	rxErr := tx.Rollback()
	if rxErr != nil {
		return -1, rxErr
	}
	return -1, resErr

}

//ExecuteSingleModels 保存多条【单模型-单记录】
func ExecuteSingleModels(singleModels SingleRowModels) (rowCount int64, err error) {
	if singleModels == nil || len(singleModels) == 0 {
		return -1, errors.New("模型集合不能为空！")
	}
	//开启事务
	tx, err := conn.Begin()
	if err != nil {
		return -1, err
	}
	var resErr error
	commitFlag := true
	rowCount = 0
	for index, model := range singleModels {
		if model.SQL == "" {
			resErr = fmt.Errorf("第%d号模型SQL不能为空", index)
			commitFlag = false
			break
		}
		//预编译语句
		smt, stmtErr := tx.Prepare(string(model.SQL))
		if stmtErr != nil {
			commitFlag = false
			resErr = stmtErr
			break
		}
		//加载参数执行语句
		rs, execErr := smt.Exec(model.Args...)
		resErr = execErr
		if execErr != nil {
			commitFlag = false
			break
		}
		count, err := rs.RowsAffected()
		resErr = err
		if err != nil {
			commitFlag = false
			break
		}
		rowCount += count
	}
	//判断是提交还是回滚
	if commitFlag {
		commitErr := tx.Commit()
		if commitErr != nil {
			return -1, commitErr
		}
		return rowCount, nil
	}
	rxErr := tx.Rollback()
	if rxErr != nil {
		return -1, rxErr
	}
	return -1, resErr

}

//ExecuteMultiModels 保存多条【单模型-多记录】
func ExecuteMultiModels(multiModels MultiRowModels) (rowCount int64, err error) {
	if multiModels == nil || len(multiModels) == 0 {
		return -1, errors.New("模型集合不能为空！")
	}
	//开启事务
	tx, err := conn.Begin()
	if err != nil {
		return -1, err
	}
	var resErr error
	commitFlag := true
	rowCount = 0
	for index, model := range multiModels {
		if model.SQL == "" {
			resErr = fmt.Errorf("第%d号模型SQL不能为空", index)
			commitFlag = false
			break
		}
		//预编译语句
		smt, stmtErr := tx.Prepare(string(model.SQL))
		if stmtErr != nil {
			commitFlag = false
			resErr = stmtErr
			break
		}
		//加载参数执行语句
		argFlag := true
		for _, args := range model.Args {
			rs, execErr := smt.Exec(args...)
			resErr = execErr
			if execErr != nil {
				argFlag = false
				break
			}
			count, err := rs.RowsAffected()
			resErr = err
			if err != nil {
				argFlag = false
				break
			}
			rowCount += count
		}
		//参数循环出错，设置参数，跳出外层模型循环
		if !argFlag {
			commitFlag = false
			break
		}
	}
	//判断是提交还是回滚
	if commitFlag {
		commitErr := tx.Commit()
		if commitErr != nil {
			return -1, commitErr
		}
		return rowCount, nil
	}
	rxErr := tx.Rollback()
	if rxErr != nil {
		return -1, rxErr
	}
	return -1, resErr

}

//createUpMapModel 创建更新SQL模型
func createUpMapModel(tableName, priKey string, dataMap map[string]interface{}) (mo SingleRowModel, err error) {
	if tableName == "" {
		return mo, errors.New("表名不能为空！")
	}
	if priKey == "" {
		return mo, errors.New("主键不能为空！")
	}
	sql := fmt.Sprintf(" UPDATE  %s ", tableName)
	colSQL := " set "
	keyValue, ok := dataMap[priKey]
	if !ok {
		return mo, errors.New("dataMap中不存在主键数据！")
	}
	for key, value := range dataMap {
		colSQL += key + " = ?,"
		mo.Args = append(mo.Args, value)
	}
	colSQL = colSQL[:len(colSQL) - 1]
	colSQL += " WHERE " + priKey + " = ? "
	mo.Args = append(mo.Args, keyValue)
	mo.SQL = SQLString(sql + colSQL)
	return mo, nil
}

//创建更新SQL模型
func createUpRecordModel(tableName, priKey string, dataMap RowRecord) (mo SingleRowModel, err error) {
	if tableName == "" {
		return mo, errors.New("表名不能为空！")
	}
	if priKey == "" {
		return mo, errors.New("主键不能为空！")
	}
	sql := fmt.Sprintf(" UPDATE  %s ", tableName)
	colSQL := " set "
	keyValue, ok := dataMap[priKey]
	if !ok {
		return mo, errors.New("dataMap中不存在主键数据！")
	}
	for key, value := range dataMap {
		colSQL += key + " = ?,"
		mo.Args = append(mo.Args, value)
	}
	colSQL = colSQL[:len(colSQL) - 1]
	colSQL += " WHERE " + priKey + " = ? "
	mo.Args = append(mo.Args, keyValue)
	mo.SQL = SQLString(sql + colSQL)
	return mo, nil
}

//创建新增SQL模型
func createNewMapModel(tableName, priKey string, dataMap map[string]interface{}) (mo SingleRowModel, err error) {
	if tableName == "" {
		return mo, errors.New("表名不能为空！")
	}
	sql := fmt.Sprintf(" INSERT INTO %s ", tableName)
	colSQL := "("
	paramsSQL := "  VALUES ("
	for key, value := range dataMap {
		colSQL += key + ","
		paramsSQL += "?,"
		mo.Args = append(mo.Args, value)
	}
	colSQL = colSQL[:len(colSQL) - 1]
	paramsSQL = paramsSQL[:len(paramsSQL) - 1]
	colSQL += ")"
	paramsSQL += ")"
	mo.SQL = SQLString(sql + colSQL + paramsSQL)
	return mo, nil
}
func createNewRecordModel(tableName, priKey string, dataMap RowRecord) (mo SingleRowModel, err error) {
	if tableName == "" {
		return mo, errors.New("表名不能为空！")
	}
	sql := fmt.Sprintf(" INSERT INTO %s ", tableName)
	colSQL := "("
	paramsSQL := "  VALUES ("
	for key, value := range dataMap {
		colSQL += key + ","
		paramsSQL += "?,"
		mo.Args = append(mo.Args, value)
	}
	colSQL = colSQL[:len(colSQL) - 1]
	paramsSQL = paramsSQL[:len(paramsSQL) - 1]
	colSQL += ")"
	paramsSQL += ")"
	mo.SQL = SQLString(sql + colSQL + paramsSQL)
	return mo, nil
}
